/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.cheln.explorer.ds.database;

import java.io.File;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.net.Uri;
import android.provider.MediaStore.Audio;
import android.widget.Toast;
import app.base.ui.NS;
import cn.cheln.explorer.config.Config;
import cn.cheln.explorer.ds.MediaArgs;
import cn.cheln.support.data.db.CursorUtil;
import cn.cheln.support.data.text.xml.SPUtil;
import cn.cheln.support.manager.ActivityPool;
import cn.power.shine.R;

import com.direction.hold.ui.CountActivity;

/**
 * @author Administrator
 */
public class SQLManager {
    public static final String unknown = "WEI ZHI";
    public static String dbfileName = "files.db";
    public static final String packageName = NS.nameSpace;
    public final static String dropFileIdxSql = "DROP INDEX path_index";
    public final static String dropFileTitleIdxSql = "DROP INDEX title_index";
    public static String SEARCH_FIELD = "distinct _data,sort_key";
    // static {
    // if (Config.upperEqual_4_0) {
    // SEARCH_FIELD += "sort_key,";
    // }
    // }
    public static String Table_image = "image";
    public static String Table_music = "files";
    public static String Table_video = "files";

    static {
        // if (Config.is4_0Lower) {
        // Table_image = "images";
        // Table_music = "audio";
        // Table_video = "video";
        // }
    }

    public static String tablename = "files";
    public static String tableimage = "image";
    public static String tabledir = "dir";
    public static String querySql = "distinct m._data _data,m.title title, m.artist_id artist_id,m.album_id album_id,m._size _size,m.date_modified date_modified,_display_name,"
            + "g.name genre,case when b.album like '<unknown>' then 'unknown'  else b.album end album,case when i.artist is null then 'unknown' when i.artist like '<unknown>' then 'unknown'  else i.artist end artist,t._data thumbnail "
            + "from audio_meta m left join (select p.audio_id _id,s.name name from audio_genres_map p left join audio_genres s on (s._id = p.genre_id)) g on (m._id = g._id) left join album_art t on (m.album_id=t.album_id ) left join artists i on (m.artist_id=i.artist_id) left join albums b on (m.album_id = b.album_id)";
    public static String createFileSortIdxSql = "CREATE INDEX IF NOT EXISTS sort_key_index ON "
            + tablename + "(sort_key)";
    public static String otherfile;
    public static File dbFile;
    public static File dbDir;
    static{
        initFileOfDB();
    }

    private static boolean initFileOfDB() {
        otherfile = android.os.Environment
                .getExternalStorageDirectory().getAbsolutePath()
                + "/shine/File"
                + "/databases/" + dbfileName;
        dbFile = new File(otherfile);
        dbDir = new File(dbFile.getParent());
        try {
            if (!dbFile.exists()) {
                dbFile.createNewFile();
            } else {
                return false;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return true;
    }

    public static Uri SelfUri = Uri
            .parse("content://com.lewa.app.filemanager/files");
    public static SQLiteDatabase sdb;

    public static String createFileTable(String tablename) {
        return "CREATE TABLE IF NOT EXISTS "
                + tablename
                + " (_id INTEGER PRIMARY KEY AUTOINCREMENT,_data TEXT,_size INTEGER,mime_type TEXT,date_modified INTEGER,title TEXT,sort_key TEXT,format INTEGER)";

    }

    public static String createIndexOfTableFile(String tablename) {
        return "CREATE INDEX IF NOT EXISTS sort_key_index ON " + tablename
                + "(sort_key)";
    }

    public static boolean createFileDB(Context context) {
        boolean firstCreated = true;
        Object dbName = SPUtil.getValue(context, "File", "dbName", String.class);
        if(dbName!=null){
            dbfileName = dbName.toString();
            firstCreated = initFileOfDB();
        }
        if (Config.is4_0Lower) {
            new File("/data/data/com.lewa.app.filemanager/databases/files.db")
                    .delete();
        }

        if (!dbDir.exists()) {
            dbDir.mkdirs();
        }
        int i = 1;boolean renamed = false;
        while(!ifCreateDBSuccessful(firstCreated)){
            dbFile.delete();
            dbfileName = dbfileName+i;
            i++;
            if(i>3){
                Toast.makeText(CountActivity.categoryActivity, R.string.createDBfailure,Toast.LENGTH_LONG).show();
                ActivityPool.getInstance().exit();
                return false;
            }
            renamed = true;
        }
        if(renamed){
            SPUtil.putValue(context, "File", "dbName", dbfileName);
        }
        Cursor fileCursor = queryTableExists(tablename);
        if (firstCreated) {
            if (fileCursor == null || fileCursor.getCount() == 0) {
                sdb.execSQL(createFileTable(tablename));
                sdb.execSQL(createIndexOfTableFile(tablename));

            }
        } else {
            if (ifIdxExists("path_index", context)) {
                sdb.execSQL(dropFileIdxSql);
            }
            if (ifIdxExists("title_index", context)) {
                sdb.execSQL(dropFileTitleIdxSql);
            }

        }

        Cursor imageCursor = queryTableExists(tableimage);
        if (imageCursor == null || imageCursor.getCount() == 0) {
            sdb.execSQL(createFileTable(tableimage));
            sdb.execSQL(createIndexOfTableFile(tableimage));
            sdb.execSQL("delete from files where mime_type like 'image%'");
        }
        Cursor dirCursor = queryTableExists(tabledir);
        if (dirCursor == null || dirCursor.getCount() == 0) {
            sdb.execSQL(createFileTable(tabledir));
            sdb.execSQL(createIndexOfTableFile(tabledir));
            sdb.execSQL("delete from files where format = 12289");
        }
        try {
            if (!ifFieldExists("files", "format", context)) {
                sdb.execSQL("alter table files add format INTEGER");
            }
            if (!ifFieldExists("files", "sort_key", context)) {
                sdb.execSQL("alter table files add sort_key TEXT");
            }
            if (!ifIdxExists("sort_key_index", context)) {
                sdb.execSQL(createIndexOfTableFile(tablename));
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        CursorUtil.closeCursor(dirCursor);
        CursorUtil.closeCursor(imageCursor);
        CursorUtil.closeCursor(fileCursor);
        sdb.close();
        return false;
    }

    private static boolean ifCreateDBSuccessful(boolean firstCreated) {
        boolean resultSuccess = true;
        try{
        sdb = firstCreated? SQLiteDatabase.openOrCreateDatabase(otherfile, null):SQLiteDatabase.openDatabase(otherfile,null,-1);
        }catch(Exception e){
            resultSuccess = false;
        }
        return resultSuccess;
    }

    private static Cursor queryTableExists(String tablename) {
        return sdb.query("sqlite_master", new String[]{"sql"},
                "type=? and name =?", new String[]{"table", tablename},
                null, null, null);
    }

    public static Cursor queryMusic(Context context, String orderby) {
        return context.getContentResolver().query(
                Audio.Media.EXTERNAL_CONTENT_URI,
                new String[]{querySql + " order by " + orderby + "--"},
                null, null, null);
    }

    public static String getSearchPart_Music_Sql(String keyWords, String orderby, String... selection) {
        return selection[0] + ", '"
                + ShowOrder.musicOrder + "' flagorder from "
                + Table_music + " where " +
                getMatchCondition(keyWords, true) +
                " and substr(mime_type,0,6) = 'audio'" + orderby;
    }

    private static String getMatchCondition(String keyWords, boolean zhFirst) {
        String sortkey = "(" + getSortKey() + " like '%*" + keyWords + "%'"
                +
                " or " +getSortKey() + " like '%|%" + keyWords + "%'"+
                ")";

        String name = "(" + getName() + " like '%" + keyWords + "%'"

                +
                ")";
        return "(" + name + " or " + sortkey + ")";
    }

    private static String getName() {
        return " title ";

    }

    public static String getSearchPart_Image_Sql(String keyWords,
                                                 String orderby, Float image_filter_size, String... selection) {
        return selection[0] + ", '"
                + SQLManager.ShowOrder.picOrder + "' flagorder from "
                + Table_image + " where " + getMatchCondition(keyWords, false) + " and _size > " + image_filter_size * 1024
                // + " and substr(mime_type,0,6) = 'image'"
                + orderby;
    }

    private static String getSortKey() {
        return "sort_key";
    }

    public static String getSearchPart_Video_Sql(String keyWords, String orderby, String... selection) {
        return selection[0] + ", '"
                + SQLManager.ShowOrder.videoOrder + "' flagorder from "
                + Table_video + " where " + getMatchCondition(keyWords, true) + " and mime_type like 'video/%'" + orderby;
    }

    public static String getSearchPart_Files_Sql(String keyWords, String orderby, String... selection) {
        return selection[0]
                + ", '"
                + SQLManager.ShowOrder.docOrder
                + "' flagorder from files where (mime_type like 'text/%' or (mime_type like 'application/%' and mime_type not like 'application/vnd.shortcut.package-archive')) and " +
                getMatchCondition(keyWords, false) + orderby;
    }

    public static String getSearchPart_Folder_Sql(String keyWords,
                                                  String orderby, String... selection) {
        return selection[0]
                + ", '"
                + ShowOrder.folderOrder
                + "' flagorder from dir where " +
                getMatchCondition(keyWords, false) + " " + orderby;
    }

    public static String getSearchPart_Apk_Sql(String keyWords, String sContent, String... selection) {
        // TODO Auto-generated method stub
        return selection[0] + ", '" + ShowOrder.apkOrder
                + "' flagorder from files where " +
                getMatchCondition(keyWords, true) + " and mime_type like '" + MediaArgs.APK_MIME + "'"
                + sContent;
    }

    public static boolean apkFlagExists(Context ctx) {
        return ifFieldExists("files", "apk_condition_install", ctx);
    }

    public static Boolean ifFieldExists(String table, String field, Context ctx) {
        boolean exist = false;
        Cursor c = ctx.getContentResolver().query(
                MediaArgs.otherUri,
                new String[]{" sql from sqlite_master where tbl_name='"
                        + table + "'--"}, null, null, null);
        if (c != null && c.getCount() > 0) {
            c.moveToNext();
            exist = c.getString(0).contains(field);
        }
        CursorUtil.closeCursor(c);
        return exist;
    }

    public static Boolean ifIdxExists(String idxname, Context ctx) {
        Cursor c = ctx
                .getContentResolver()
                .query(MediaArgs.otherUri,
                        new String[]{" 1 from sqlite_master where type='index' and name='"
                                + idxname + "'--"}, null, null, null);
        boolean b = c != null && c.getCount() > 0;
        CursorUtil.closeCursor(c);
        return b;
    }

    public static final class ShowOrder {
        public static int picOrder = 0;
        public static int musicOrder = 1;
        public static int videoOrder = 2;
        public static int docOrder = 3;
        public static int apkOrder = 4;
        public static int folderOrder = -1;
    }


}